package com.howard.app.nivada;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.howard.app.nivada.bo.Group;
import com.howard.app.nivada.bo.Product;
import com.howard.app.nivada.bo.Store;
import com.howard.tools.file.ExcelHelper;
import com.howard.tools.file.FileHelper;
import com.howard.tools.file.JacobExcelHelper;
import com.howard.tools.file.bo.ExcelMultipleBeanTemplate;

public class CoverageAnalyzer extends ExcelHelper {
	
	public static final DateFormat MONTH_DAY_DF = new SimpleDateFormat("MMdd");
	public static final String TEMPLATE_SHEET_NAME_STANDARD = "STANDARD";
	public static final String TEMPLATE_SHEET_NAME_STORE = "STORE";
	public static final String TEMPLATE_FILE = "Nivada_template.xlsx";
	public static final String STANDARD_FILE = "出样标准.xlsx";
//	public static final String TEMPLATE_FILE = "resources/Nivada_template.xlsx";
//	public static final String STANDARD_FILE = "resources/出样标准.xlsx";
	public static final String CONFIG_FILE = "config.properties";
	public static final String KEY_INPUT = "input";
	public static final String KEY_OUTPUT = "output";
	public static final String IN_FOLDER = "c:/tmp/input";
	public static final String OUT_FOLDER = "c:/tmp/output";
	public static final String SUMMARY_FILE = "库存盘查汇总表.xlsx"; 
//	boolean isRunByJar = false;
	
	private static void analyzeProductCoverage() throws Exception{
		File jarFolder = getJarFolder();
		
		File inputFolder = null;
		File outputFolder = null;
		File configFile = new File(jarFolder.getParentFile().getAbsoluteFile() + File.separator + CONFIG_FILE);
		if(configFile.exists()){
			Map<String, String> configMap = getConfigMap(configFile);
			inputFolder = new File(configMap.get(KEY_INPUT));
			outputFolder = new File(configMap.get(KEY_OUTPUT));
		}
		else{
			inputFolder = new File(IN_FOLDER);
			outputFolder = new File(OUT_FOLDER);
		}
		
		File tmpInputFolder = new File(inputFolder.getAbsolutePath().replaceAll("input", "input_converted"));
		System.out.println("输入目录:"+inputFolder.getAbsolutePath());
		System.out.println("格式化后输入目录:"+tmpInputFolder.getAbsolutePath());
		System.out.println("输出目录:"+outputFolder.getAbsolutePath());
		System.out.println();
		
		
		JacobExcelHelper.converFiles(inputFolder, tmpInputFolder);
		
		
		if(outputFolder.exists() == false){
			outputFolder.mkdirs();
		}
		for(File file: outputFolder.listFiles()){
			file.delete();
		}
		String monthDayStr = MONTH_DAY_DF.format(new Date());
		
		Workbook standardWb = WorkbookFactory.create(FileHelper.getResourceInputStream(STANDARD_FILE));
		Sheet productStanderSheet = standardWb.getSheetAt(0);
		List<Product> standardProductList = new ArrayList<Product>();
		for(int i = 2 ; i <= productStanderSheet.getLastRowNum(); i ++){
			Row row = productStanderSheet.getRow(i);
			String code = ExcelHelper.getCellStringValue(row.getCell(1));
			if(FileHelper.isBlankStr(code)){
				continue;
			}
			
			String priceStr = ExcelHelper.getCellStringValue(row.getCell(2));
			Double price = FileHelper.isBlankStr(priceStr)?null:Double.parseDouble(priceStr);
			String specialPriceStr = ExcelHelper.getCellStringValue(row.getCell(3));
			Double specialPrice = FileHelper.isBlankStr(specialPriceStr)?null:Double.parseDouble(specialPriceStr);
			String remark = ExcelHelper.getCellStringValue(row.getCell(4));
			
			Product product = new Product();
			product.setCode(code);
			product.setPrice(price);
			product.setSpecialPrice(specialPrice);
			product.setRemark(remark);
			standardProductList.add(product);
		}
		List<Store> summaryStoreList = new ArrayList<Store>();
		for(File districtFolder: tmpInputFolder.listFiles()){
			System.out.println("正在处理 区域:" + districtFolder.getName() + " .......");
			File outFile = new File(outputFolder.getAbsolutePath()+File.separator+districtFolder.getName()+"区域库存盘查"+monthDayStr+".xlsx");
			List<Store> districtStoreList = analyzeDistrict(districtFolder, outFile, standardProductList);
			summaryStoreList.addAll(districtStoreList);
		}
		System.out.println("=========summaryStoreList.size="+summaryStoreList.size());
		processSummaryFile(summaryStoreList, monthDayStr);
	}
	
	
	private static List<Store> analyzeDistrict(File districtFolder, File outFile, 
			List<Product> standardProductList) throws Exception{
		List<Store> returnStoreList = new ArrayList<Store>();
		if(districtFolder.listFiles().length == 0){
			return returnStoreList;
		}
//		List<Sheet> districtSheetList = new ArrayList<Sheet>();
		List<Store> storeList = new ArrayList<Store>();
		for(File districtFile: districtFolder.listFiles()){
//			System.out.println(districtFile.getAbsolutePath());
//			Sheet sheet = WorkbookFactory.create(FileHelper.getResourceInputStream(districtFile.getAbsolutePath())).getSheetAt(0);
			Sheet sheet = ExcelHelper.getWorkBook(districtFile).getSheetAt(0);//WorkbookFactory.create(new POIFSFileSystem(new FileInputStream(districtFile))).getSheetAt(0);
			if(sheet.getLastRowNum() < 6){
				continue;
			}
			String storeDesc = sheet.getRow(4).getCell(0).toString();
//			System.out.println("storeDesc="+storeDesc);
			int idx = storeDesc.lastIndexOf("-");
			if(idx < 0){
				idx = storeDesc.lastIndexOf("－"); 
			}
			String storeName = storeDesc.substring(idx+1);
			
			Store store = new Store();
			store.setName(storeName);
			store.setDesc(storeDesc);
			store.setDistrict(districtFolder.getName());
			
			for(int j = 8 ; j < sheet.getLastRowNum(); j ++){
				Row row = sheet.getRow(j);
				String code = ExcelHelper.getCellStringValue(row.getCell(2));
				String type = ExcelHelper.getCellStringValue(row.getCell(3));
				String countStr = ExcelHelper.getCellStringValue(row.getCell(5));
				Integer count = FileHelper.isBlankStr(countStr)?null:Integer.parseInt(countStr);
				String priceStr = ExcelHelper.getCellStringValue(row.getCell(6));
				Double price = FileHelper.isBlankStr(priceStr)?null:Double.parseDouble(priceStr);
				Product product = new Product();
				product.setCode(code);
				product.setType(type);
				product.setCount(count);
				product.setPrice(price);
				store.addProduct(product);
			}
			Collections.sort(store.getGroupList());
			
			//缺少出样型号
			List<Product> missingProductList = new ArrayList<Product>();
			List<Product> tempProductList = new ArrayList<Product>();
			for(Product standardProduct: standardProductList){
				boolean isExist = false;
				for(Product product: store.getGroup(Group.GROUP_NAMES[0]).getProductList()){
					if(standardProduct.getCode().equals(product.getCode())){
						isExist = true;
						tempProductList.add(product);
						break;
					}
				}
				
				if(isExist == false){
					missingProductList.add(standardProduct);
				}
			}
			store.setMissingProductList(missingProductList);
			
			System.out.println("		商店名称:"+storeName+",文件名:"+districtFile.getAbsolutePath());
//			store.getGroup(Group.GROUP_NAMES[0]).getProductList().removeAll(tempProductList);
			storeList.add(store);
			
		}
		
		List<String> templateSheetNameList = new ArrayList<String>();
		List<String> resultSheetList = new ArrayList<String>();
		
		
		//fill sheet STANDARD
		List<Map<String, Object>> beansMapList = new ArrayList<Map<String, Object>>();
		templateSheetNameList.add(TEMPLATE_SHEET_NAME_STANDARD);
		resultSheetList.add("出样标准");
		Map<String, Object> standardBeansMap = new HashMap<String, Object>();
		standardBeansMap.put("standardProductList", standardProductList);
		beansMapList.add(standardBeansMap);
		
		for(Store store: storeList){
			boolean isStoreAdded = false;
			//filter store with same name
			for(String addedStoreName: resultSheetList){
				if(addedStoreName.equals(store.getName())){
					isStoreAdded = true;
				}
			}
			
			if(isStoreAdded){
				System.out.println("		过滤同名店名:"+store.getName());
				continue;
			}
//			System.out.println("add store:"+store.getName());
//			for(Group group: store.getGroupList()){
//				System.out.println("groupName="+group.getName());
//				System.out.println("productList="+group.getProductList());
//			}
			
			templateSheetNameList.add(TEMPLATE_SHEET_NAME_STORE);
			resultSheetList.add(store.getName());
			Map<String, Object> storeBeansMap = new HashMap<String, Object>();
			storeBeansMap.put("store", store);
			Double okRate = (double)store.getGroup(Group.GROUP_NAMES[0]).getTypeCount()/(double)standardProductList.size() ;
			store.setOkRate(okRate);
			storeBeansMap.put("okRate", okRate);
			storeBeansMap.put("GROUP_NAMES", Group.GROUP_NAMES);
			storeBeansMap.put("standardCount", standardProductList.size());
			beansMapList.add(storeBeansMap);
			returnStoreList.add(store);
		}
//		System.out.println("templateSheetNameList="+templateSheetNameList);
//		System.out.println("resultSheetList="+resultSheetList);
//		System.out.println("beansMapList="+beansMapList);
		ExcelMultipleBeanTemplate excelMultipleBeanTemplate = new ExcelMultipleBeanTemplate(templateSheetNameList, resultSheetList, beansMapList);
		ExcelHelper.generateExcel(FileHelper.getResourceInputStream(TEMPLATE_FILE), outFile.getAbsolutePath(), excelMultipleBeanTemplate);
		return returnStoreList;
	}
	
	public static File getJarFolder() {
        URL url = CoverageAnalyzer.class.getProtectionDomain().getCodeSource().getLocation();
        return new File(url.getFile()).getParentFile();
    }
	
	private static Map<String, String> getConfigMap(File configFile) throws Exception{
		List<String> configStrList = FileHelper.getListFileContents(configFile.getAbsolutePath());
		Map<String, String> configMap = new HashMap<String, String>();
		for(String keyValueStr: configStrList){
			String[] keyValueArr = keyValueStr.split(FileHelper.PROPERTY_DELIMITER);
			configMap.put(keyValueArr[0], keyValueArr[1]);
		}
		return configMap;
	}
	
	private static void processSummaryFile(List<Store> summaryStoreList, String monthDayStr) throws Exception{
		System.out.println("=========processSummaryFile");
		String inputFile = IN_FOLDER + File.separator + SUMMARY_FILE;
		String outputFile = OUT_FOLDER + File.separator + SUMMARY_FILE.replace(".xls", monthDayStr+".xls");
		System.out.println(new File(inputFile).getAbsolutePath());
//		Workbook wb = WorkbookFactory.create(new FileInputStream(inputFile));
		Workbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
		System.out.println("wb="+wb);
		for(int sheetNo = 0 ; sheetNo < wb.getNumberOfSheets(); sheetNo ++){
			Sheet sheet = wb.getSheetAt(sheetNo);
			String u8ColName = ExcelHelper.getCellStringValue(sheet, 2, 2);
//			System.out.println("sheetName="+sheet.getSheetName()+",u8ColName="+u8ColName);
			
			if(!"U8仓库编码".equals(u8ColName)){
				System.out.println("库存盘查计划表-->未处理区域:"+sheet.getSheetName());
				continue;
			}
			System.out.println("库存盘查计划表-->正在处理区域:"+sheet.getSheetName());
			for (int rowNo = 3; rowNo <= sheet.getLastRowNum()-1; rowNo++) {
				String u8Code = ExcelHelper.getCellStringValue(sheet, rowNo, 2);
				String storeName = ExcelHelper.getCellStringValue(sheet, rowNo, 4);
				if(FileHelper.isBlankStr(u8Code)){
					System.out.println("	U8仓库编码为空:"+storeName);
					continue;
				}
				Store tmpStore = getStoreByU8Code(summaryStoreList, u8Code);
				if(tmpStore == null){
					System.out.println("	不存在的U8仓库编码:"+u8Code);
					continue;
				}
				ExcelHelper.setCell(sheet, rowNo, 9, tmpStore.getCount()+"");
				ExcelHelper.setCell(sheet, rowNo, 10, tmpStore.getMissingProductList().size()+"");
				ExcelHelper.setCell(sheet, rowNo, 11, tmpStore.getOkRate()+"");
				System.out.println("	已处理U8仓库编码:"+u8Code);
			}
		}
		FileOutputStream fout = new FileOutputStream(outputFile);  
        wb.write(fout);  
        fout.close(); 
	}
	
	private static Store getStoreByU8Code(List<Store> summaryStoreList, String u8Code){
		for(Store summaryStore: summaryStoreList){
			String tmpStoreDesc = summaryStore.getDesc();
			String tmpU8Code = tmpStoreDesc.substring(tmpStoreDesc.indexOf("(")+1,tmpStoreDesc.indexOf(")"));
			if(tmpU8Code.equals(u8Code)){
				return summaryStore;
			}
		}
		return null;
	}
	
//	run by jar
	public static void main(String[] args) {
		try{
			long start = System.currentTimeMillis();
			analyzeProductCoverage();
			
			System.out.println("结束, 花费时间(毫秒):"+(System.currentTimeMillis()-start));
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	//run by Eclipse
//	public static void main(String[] args) {
//		try{
//			long start = System.currentTimeMillis();
//			File inputFolder = new File("G:/tmp/input");
//			File outputFolder = new File("G:/tmp/output");
//			for(File dataFile: inputFolder.listFiles()){
//				System.out.println("正在处理 " + dataFile.getName() + " .......");
//				analyzeProductCoverage(dataFile, outputFolder,TEMPLATE_FILE);
//			}
//			System.out.println("结束, 花费时间(毫秒):"+(System.currentTimeMillis()-start));
//		}
//		catch(Exception e){
//			e.printStackTrace();
//		}
//	}

}



